﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Maxleaf.EDI.API.Contract.K3.Commands;
using Maxleaf.EDI.DataAccess.UnitOfWork;
using Maxleaf.EDI.Domain;
using Maxleaf.EDI.Domain.EDI;
using Maxleaf.EDI.Domain.K3;
using Maxleaf.EDI.Infrastructure.Exceptions;
using MaxLeaf.Common;
using MaxLeaf.DB;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Excel = Microsoft.Office.Interop.Excel;

namespace Maxleaf.EDI.Core.Handlers.CommandHandlers
{
    public class ImportSalesOrderCommandHandler : ICommandHandler<ImportSalesOrderCommand>
    {
        public string mstrFieldNamesHDR_K3 = @" FBrNo,finterid,fbillno,Fcurrencyid,FcustID,Fdate,FsaleStyle,FdeptID,FEmpID,Fcheckerid,fbillerid
                                                ,Ftrantype,finvoiceclosed,fbclosed,fmangerid,fsettleid,fexchangerate,fdiscounttype,fstatus,fcancellation
                                                ,Fcheckdate,Fsettledate,fseltrantype,fchildren,fbrid,fareaps,fsysstatus                                                
                                                ,fheadselfS0160,fheadselfS0161,fheadselfS0162,FHeadSelfS0150,FHeadSelfS0151,FHeadSelfS0153
                                                ,fheadselfS0163,fheadselfS0164,fheadselfS0165,fheadselfS0166,fheadselfS0167,FHeadSelfS0168,FHeadSelfS0157,FHeadSelfS0169";

        public string mstrFieldNamesDTL_K3 = @"FBrNO,FInterID,FEntryID,FItemID,FQty,FCommitQty,FPrice,FAmount,FTaxRate,FTaxAmount,FTax,FDiscount,FNote,FDate,
                                                FDiscountAmount,FInvoiceQty,FBCommitQty,FTranLeadTime,FATPDeduct,FCostObjectID,FUnitID,FAuxPrice,FAuxQty,FCESS,
                                                FAllAmount,FAllStdAmount,FAuxPriceDiscount,FPriceDiscount,FTaxAmt,FAuxTaxPrice,FTaxPrice,FDetailID,FMrpAutoClosed,
                                                FSecStockQty,FSecInvoiceQty,FSecCommitInstall,FPlanMode,FEntrySelfS0161,FEntrySelfS0162,FEntrySelfS0163,
                                                FEntrySelfS0168,FEntrySelfS0170,FEntrySelfS0171,FAdviceConsignDate,FStockQty,FAuxStockQty,FMrpClosed";


        public struct OrderDetail
        {
            /// <summary>
            /// 内部单号
            /// </summary>
            public string FInterID;

            /// <summary>
            /// 行号
            /// </summary>
            public string FEntryID;

            /// <summary>
            /// 物料ID
            /// </summary>
            public string FItemID;

            /// <summary>
            /// 物料类型ID，存在于t_icitem中，用于表示属于哪个类型，从而决定这个物料的折扣，价格表：icdisplyentry
            /// </summary>
            public string FItemTypeID;    // 物料类型ID，存在于t_icitem中，用于表示属于哪个类型，从而决定这个物料的折扣，价格表：icdisplyentry

            /// <summary>
            /// 基本单位的数量
            /// </summary>
            public double FBaseQty;

            /// <summary>
            /// 销售记量单位    
            /// </summary>
            public string FAuxUnitID;      //销售记量单位    

            /// <summary>
            /// 销售记量单位的价格
            /// </summary>
            public double FAuxPrice;       //销售记量单位的价格

            /// <summary>
            /// 销售记量单位的数量
            /// </summary>
            public double FAuxQty;       //销售记量单位的数量


            /// <summary>
            /// 基本记量单位 ,对应K3的 FUnitID
            /// </summary>
            public string FBaseUnitID;      //基本记量单位    

            /// <summary>
            /// 基本记量单位的价格, 对应K3的 FPrice
            /// </summary>
            public double FBasePrice;       //基本记量单位的价格

            /// <summary>
            /// 税率 
            /// </summary>
            public double FTaxRate;     //税率

            ///<summary>
            ///税率总金额   FBaseAmount * FTaxRate
            /// </summary>            
            public double FTaxAmount;


            /// <summary>
            /// 折扣率
            /// </summary>
            public double FDisRate;     // 折扣率

            /// <summary>
            /// 折扣金额  对应金蝶中的 FTaxAmount ，  出厂价*税率
            /// </summary>
            public double FDisAmt;     // 折扣金额

            /// <summary>
            /// 折扣价格  
            /// </summary>
            public double FDisPrice;     // 折扣价格

            /// <summary>
            /// 交货日期 
            /// </summary>
            public DateTime FDate;   //交货日期 

            /// <summary>
            /// 建议交货日期 
            /// </summary>
            public DateTime FAdviceConsignDate;   //建议交货日期 

            /// <summary>
            /// 扣税后的金额  FBaseQty * FBasePrice * (1- FDisRate*0.01)
            /// </summary>
            public double FBaseAmount;

            /// <summary>
            /// 出厂价格*单位Rate
            /// </summary>
            public double FAuxTaxPrice;

            /// <summary>
            /// 出厂价格
            /// </summary>
            public double FTaxPrice;

            /// <summary>
            /// 出厂单价 -- 0.72   ？？？？？？  是不是应该是金额来着，需要确认
            /// </summary>
            public double FEntrySelfS0161;

            /// <summary>
            /// 出厂实价 -- 0.72*(1-0.05) =0.684
            /// </summary>
            public double FEntrySelfS0162;

            /// <summary>
            /// 出厂金额 -- 4320 * 0.72   FQty*FEntrySelfS0161
            /// </summary>
            public double FEntrySelfS0163;

            /// <summary>
            /// 
            /// </summary>
            public double FCESS;

            /// <summary>
            /// 物料的颜色
            /// </summary>
            public string FEntrySelfS0168;

            /// <summary>
            /// 重量  
            /// </summary>
            public string FEntrySelfS0170;


            /// <summary>
            /// 记录网站中行的rec_id
            /// </summary>
            public int FEntrySelfS0171;


            /// <summary>
            /// AuxUnit和BaseUnit之间的转换关系
            /// </summary>
            public double dblRate;

            /// <summary>
            /// 传递mary_order_info表中tel、mobile字段传递到k3销售订单上的客户电话（FHeadSelfS0157）字段，传递到k3后的格式为：tel，mobile
            /// </summary>
            public string FHeadSelfS0157;

        }

        private readonly IUnitOfWorkProvider unitOfWorkProvider;

        private readonly IRepository<EX_Bill> eX_BillRepository;

        private string GetTableSQL(DBAccess dbcon, string strTableName)
        {
            DataTable dt = dbcon.GetDataTable("select top 0 * from  " + strTableName);
            string strFields = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].Caption.ToUpper().Trim() != "Recid".ToUpper() && dt.Columns[i].Caption.ToUpper().Trim() != "RecStamp".ToUpper())
                {
                    strFields += (strFields != "" ? "," : "") + "[" + dt.Columns[i].Caption + "]";
                }
            }
            return strFields;
        }

        public ImportSalesOrderCommandHandler(
            IRepository<EX_Bill> eX_BillRepository,
            IUnitOfWorkProvider unitOfWorkProvider)
        {
            this.eX_BillRepository = eX_BillRepository;
            this.unitOfWorkProvider = unitOfWorkProvider;
        }

        private string GetFinterID(DBAccess dbCon)
        {
            //GetICMaxNum
            // ALTER PROCEDURE [dbo].[GetICMaxNum]
            //@TableName VARCHAR (50),  
            //@FInterID  int output,    
            //@Increment INT = 1 ,   
            //@UserID    int =0 ---增加用户ID
            string strFinterID = "";
            strFinterID = dbCon.GetValue("exec GetICMaxNum @TableName,@FInterID output,@Increment,@UserID"
                  , new SqlParameter[] {
                        new SqlParameter("@TableName", "SEORDER")
                        ,new SqlParameter("@FInterID", System.Data.SqlDbType.Int)
                        ,new SqlParameter("@Increment","1")
                        ,new SqlParameter("@UserID","0")                        
                    }
                  , "FInterID");

            return strFinterID;
        }
        private string GetBillNo(DBAccess dbCon)
        {
            //geticbillno('0','81' )
            // strSql = "UnitCnv '" + strItemNo + "'," + dFromQty + ",'" + strFromUnit + "','" + strToUnit + "'";
            // dToQty = dBAccess.GetDoubleValue(strSql);

            string strBillNo = "";
            //string strSQL = " GetIcBillNo '0', '81', '" + strBillNo + "'";
            //strBillNo = dbCon.GetValue(strSQL);

            strBillNo = dbCon.GetValue("exec GetIcBillNo @IsSave,@FBillType,@BillId output"
                    , new SqlParameter[] {
                        new SqlParameter("@IsSave", "1")
                        ,new SqlParameter("@FBillType","81")
                        ,new SqlParameter("@BillID",System.Data.SqlDbType.VarChar,50)
                    }
                    , "BillID");

            string strMaxValue = strBillNo.Substring(5, strBillNo.Length - 6);
            strBillNo = "SEORD" + strMaxValue.PadLeft(6, '0');
            dbCon.ExecuteSql("update t_BillCodeRule set FProjectVal = FProjectVal+1 where FBillTypeID=81 and FClassIndex=2 and FProjectID=3");
            dbCon.ExecuteSql("update ICBillNo set FDesc='SEORD+'+'" + strMaxValue.PadLeft(6, '0') + "' where FBillID=81");

            return strBillNo;
        }


        private double GetDefPrice(DBAccess dbCon, OrderDetail newOrderDetail, DataRow drHdr, ref string strUnit)
        {
            string strSQL = @" select top 1 * from(
                            select t.fnumber as fnumber ,t.fname as fname ,m.fname as mfname,m.fitemid as funitid ,ic.fbegdate as fbegdate,ic.fprice  as fprice  from icprcplyentry ic
                            left join t_organization o on ic.Frelatedid = o.FTypeID  
                            left join t_icitem t on ic.fitemid=t.fitemid 
                            left join t_measureunit m on m.fmeasureunitid= ic.funitid  
                            where ic.finterid=6 and o.fItemID='{0}' and  t.fItemID='{1}' )as ass
                            order by fbegdate desc ";

            strSQL = string.Format(strSQL, drHdr["FCustID"], newOrderDetail.FItemID);
            DataRow drPrice = dbCon.GetDataFirstRow(strSQL);
            if (drPrice != null)
            {
                strUnit = CvtFunc.ToString(drPrice["funitid"]);
                return CvtFunc.ToDouble(drPrice["fprice"]);
            }
            else
            {
                return 0;
            }
        }

        private double GetDefDiscount(DBAccess dbCon, OrderDetail newOrderDetail, DataRow drHdr)
        {
            double dblValue = 0;
            string strSQL = @"select a.fvalue,t.fnumber,t.fname,o.fnumber,o.fname ,* from ICDisPlyentry a
                            left join  t_icitem t on a.fitemid=t.ftypeid
                            left join  t_organization o on a.frelatedid=o.fitemid
                            where a.finterid=4 and t.fitemid='{0}' and o.fitemid='{1}'";
            DataRow drRow = dbCon.GetDataFirstRow(string.Format(strSQL, newOrderDetail.FItemID, drHdr["FCustID"]));
            if (drRow != null)
            {
                dblValue = CvtFunc.ToDouble(drRow["fvalue"]);
            }
            else
            {

                strSQL = @"select a.fvalue,t.fnumber,t.fname,o.fnumber,o.fname ,* from ICDisPlyentry a
                        left join  t_icitem t on a.fitemid=t.ftypeid
                        left join  t_organization o on a.frelatedid=o.ftypeid
                        where a.finterid=1  and t.fitemid='{0}' and o.fitemid='{1}' ";
                drRow = dbCon.GetDataFirstRow(string.Format(strSQL, newOrderDetail.FItemID, drHdr["FCustID"]));
                if (drRow != null)
                {
                    dblValue = CvtFunc.ToDouble(drRow["fvalue"]);
                }
            }
            return dblValue;
        }


        #region OldGetDefDiscount
        //private double GetDefDiscount(DBAccess dbCon, OrderDetail newOrderDetail,DataRow drHdr)
        //{
        //    //select * from icdisplyentry where frelatedid =40001 and FItemID=20013  -- FItemID 指的是物料类型 , frelatedid是指客户的类型
        //    //select * from icdisply
        //    //优先高的先取，找不到就向下一个级别找 
        //    //FSysTypeID这个如果=1 表示Freletedid 是客户内码，=501表示Freletedid 取客户类别
        //    //FItemtypeID=4表示itemid表示物料Fitemid，=504表示物料类别
        //    double dblFvalue = 0;
        //    string strSQL = "";
        //    strSQL = "select * from icdisply order by Fpri";
        //    DataTable dtICDIS = dbCon.GetDataTable(strSQL);
        //    foreach (DataRow drDic in dtICDIS.Rows)
        //    {
        //        string strItem = "";
        //        string strCust = "";
        //        bool blnUseQty = false;
        //        string strCustTypeID =dbCon.GetValue ("select ftypeid from t_organization where fitemid='" + drHdr["FCustID"].ToString() + "'");
        //        strSQL = "select * from icdisplyentry where frelatedid ='{0}' and  FItemID='{1}'";
        //        strSQL += " and FBegDate<=getdate() and FEndDate>=getdate()  and FinterID='{3}' and FChecked=1";
        //        if (CvtFunc.ToString(drDic["FSysTypeID"]) == "1") //是客户内码
        //        {
        //            strCust = drHdr["FCustID"].ToString();
        //        }
        //        else if (CvtFunc.ToString(drDic["FSysTypeID"]) == "501") //取客户类别
        //        {
        //            strCust = drHdr["CustTypeID"].ToString(); 
        //        }

        //        if (CvtFunc.ToString(drDic["FItemtypeID"]) == "4") //物料Fitemid
        //        {
        //            strItem = newOrderDetail.FItemID;
        //            blnUseQty = true;
        //        }
        //        else if (CvtFunc.ToString(drDic["FItemtypeID"]) == "504") //物料类别
        //        {
        //            strItem = newOrderDetail.FItemTypeID;
        //        }
        //        if (blnUseQty) strSQL += " and FUnitID='{2}' ";
        //        string strSQLUnit = string.Format(strSQL, strCust, strItem, newOrderDetail.FAuxUnitID, CvtFunc.ToString(drDic["FinterID"]));
        //        DataTable dtDisc = dbCon.GetDataTable(strSQLUnit);
        //        double dblQty = newOrderDetail.FAuxQty; // 如果没有这个单位的，则用BaseQty                
        //        if (dtDisc.Rows.Count == 0)
        //        {
        //            strSQLUnit = string.Format(strSQL, strCust, strItem, newOrderDetail.FBaseUnitID, CvtFunc.ToString(drDic["FinterID"]));
        //            dtDisc = dbCon.GetDataTable(strSQLUnit);
        //            dblQty = newOrderDetail.FAuxQty * newOrderDetail.dblRate;
        //        }
        //        DataView dvDisc = null;
        //        if (dtDisc.Rows.Count > 0)
        //        { dvDisc = dtDisc.DefaultView; }
        //        foreach (DataRow drRow in dtDisc.Rows)
        //        {
        //            if (blnUseQty)
        //            {
        //                dvDisc.RowFilter = "FBegQty<='" + dblQty + "' and FEndQty>='" + dblQty + "'";
        //            }
        //            else
        //            {
        //                dvDisc.RowFilter = "FBegAmt<='" + newOrderDetail.FAuxQty * newOrderDetail.FAuxTaxPrice + "' and FEndAmt>='" + newOrderDetail.FAuxQty * newOrderDetail.FAuxTaxPrice + "'";
        //            }
        //            if (dvDisc.Count > 0)
        //            {
        //                dblFvalue = CvtFunc.ToDouble(dvDisc[0]["Fvalue"]);
        //                return dblFvalue;
        //            }
        //            else
        //            {
        //                dvDisc.RowFilter = "";
        //                if (blnUseQty)  // 数量的范围
        //                {
        //                    if (CvtFunc.ToDouble(dvDisc[0]["FBegQty"]) == 0 && CvtFunc.ToDouble(dvDisc[0]["FEndQty"]) == 0)
        //                    {
        //                        dblFvalue = CvtFunc.ToDouble(dvDisc[0]["Fvalue"]);
        //                        return dblFvalue;
        //                    }
        //                    else
        //                    {
        //                        continue;
        //                    }
        //                }
        //                else  // 金额的范围
        //                {
        //                    if (CvtFunc.ToDouble(dvDisc[0]["FBegAmt"]) == 0 && CvtFunc.ToDouble(dvDisc[0]["FEndAmt"]) == 0)
        //                    {
        //                        dblFvalue = CvtFunc.ToDouble(dvDisc[0]["Fvalue"]);
        //                        return dblFvalue;
        //                    }
        //                    else
        //                    {
        //                        continue;
        //                    }
        //                }
        //            }
        //        }
        //        if (dblFvalue > 0) break;
        //    }
        //    return dblFvalue;
        //}
        #endregion

        private bool ImportFromExcelHdr(DBAccess dbcon, ImportSalesOrderCommand command, DataRow drHdr, string strTableName)
        {
            using (FileStream stream = File.OpenRead(command.FileName))   //打开myxls.xls文件
            {
                IWorkbook workbook = new HSSFWorkbook(stream);//从流内容创建Workbook对象
                ISheet sheet = workbook.GetSheetAt(0);//获取第一个工作表

                using (var ediUnitOfWork = this.unitOfWorkProvider.GetEDIUnitOfWork())
                {

                    //得到所有EX_Bill记录
                    var eX_Bills = this.eX_BillRepository.GetAll(ediUnitOfWork);

                    //用linq查询
                    var dtConfigure = eX_Bills.Where(o => o.DBID == command.DBId && o.FTranType == "81" && o.FTableName == strTableName).ToList();
                    int intRow = 0;
                    int intCol = 0;

                    foreach (EX_Bill bill in dtConfigure.ToArray())
                    {
                        intRow = bill.ExRow;
                        intCol = bill.ExCol;
                        IRow row = sheet.GetRow(intRow - 1);//获取工作表第一行
                        ICell cell = row.GetCell(intCol - 1);//获取行的第一列 

                        try
                        {
                            string strCustFNumber = CvtFunc.ToString(cell.ToString());
                            //if ( CvtFunc.ToString ( bill.TransString) != "")
                            //{
                            //    string strTransString = "";
                            //    drHdr[bill.KField] = CvtFunc.ToString(dbcon.GetValue( string.Format (strTransString, strCustFNumber) ));
                            //    if (CvtFunc.ToString(drHdr[bill.KField]).Trim() == "")
                            //    {
                            //        throw new MaxLeafException( bill.KField.ToString() + "不存在或者可能已被删除." + cell.ToString());
                            //    }
                            //}
                            //else 
                            if (bill.KField.ToString().Trim().ToUpper() == "FCUSTID")
                            {
                                drHdr[bill.KField] = CvtFunc.ToString(dbcon.GetValue("select fitemid from t_organization where fnumber='" + strCustFNumber + "'"));
                                if (CvtFunc.ToString(drHdr[bill.KField]) == "")
                                {
                                    throw new MaxLeafException("客户不存在." + cell.ToString());
                                }
                            }
                            else if (bill.KField.ToString().ToUpper() == "FdeptID".ToString().ToUpper())
                            {
                                drHdr["FdeptID"] = CvtFunc.ToString(dbcon.GetValue("select FItemID from t_department where FDeleted=0 and fnumber='" + cell.ToString() + "'"));
                                if (CvtFunc.ToString(drHdr["FdeptID"]).Trim() == "")
                                {
                                    throw new MaxLeafException("部门不存在或者可能已被删除." + cell.ToString());
                                }
                            }
                            else if (bill.KField.ToString().ToUpper() == "FEmpID".ToString().ToUpper())
                            {
                                drHdr["FEmpID"] = CvtFunc.ToString(dbcon.GetValue("Select fitemid from t_emp where  FDeleted=0 and fnumber='" + cell.ToString() + "'"));
                                if (CvtFunc.ToString(drHdr["FEmpID"]) == "")
                                {
                                    throw new MaxLeafException("员工不存在或者可能已被删除." + cell.ToString());
                                }
                            }
                            else if (bill.KField.ToString().ToUpper() == "fmangerid".ToString().ToUpper())
                            {
                                drHdr["fmangerid"] = CvtFunc.ToString(dbcon.GetValue("Select fitemid from t_emp where  FDeleted=0 and fnumber='" + cell.ToString() + "'"));
                                if (CvtFunc.ToString(drHdr["fmangerid"]) == "")
                                {
                                    throw new MaxLeafException("主管不存在或者可能已被删除." + cell.ToString());
                                }
                            }
                            else if (bill.KField.ToString().ToUpper() == "FHeadSelfS0151".ToString().ToUpper())
                            {
                                drHdr["FHeadSelfS0151"] = CvtFunc.ToString(dbcon.GetValue("select fitemid from t_item where fitemclassid=3007 and fnumber='" + cell.ToString() + "'"));
                            }
                            else if (bill.KField.ToString().ToUpper() == "FHeadSelfS0169".ToString().ToUpper())
                            {
                                drHdr["FHeadSelfS0169"] = CvtFunc.ToString(dbcon.GetValue("select fitemid from t_organization where fnumber='" + cell.ToString() + "'"));
                            }
                            else if (bill.KField.ToString().ToUpper() == "FHeadSelfS0170".ToString().ToUpper())
                            {
                                drHdr["FHeadSelfS0170"] = CvtFunc.ToString(dbcon.GetValue("select fname from t_organization where  fnumber='" + cell.ToString() + "'"));
                            }
                            else
                            {
                                if (drHdr.Table.Columns[bill.KField.ToString()].DataType.ToString() == "System.DateTime")
                                {
                                    drHdr[bill.KField.ToString()] = cell.DateCellValue;
                                }
                                else
                                {
                                    drHdr[bill.KField.ToString()] = strCustFNumber;
                                }
                            }
                        }
                        catch (Exception ex)
                        {

                            throw new MaxLeafException(bill.KField.ToString() + "不存在.");
                        }
                        finally
                        {

                        }
                    }
                }
            }
            return true;
        }

        private bool ImportFromExcelDtl(DBAccess dbcon, ImportSalesOrderCommand command, DataRow drHdr, string strTableName)
        {
            using (FileStream stream = File.OpenRead(command.FileName))   //打开myxls.xls文件
            {
                IWorkbook workbook = new HSSFWorkbook(stream);//从流内容创建Workbook对象
                ISheet sheet = workbook.GetSheetAt(0);//获取第一个工作表

                using (var ediUnitOfWork = this.unitOfWorkProvider.GetEDIUnitOfWork())
                {
                    //得到所有EX_Bill记录
                    var eX_Bills = this.eX_BillRepository.GetAll(ediUnitOfWork);

                    //用linq查询
                    var dtConfigure = eX_Bills.Where(o => o.DBID == command.DBId && o.FTranType == "81" && o.FTableName == strTableName).OrderBy(o => o.ExCol).ToList();
                    int intRow = 1;
                    int intCol = 1;
                    try
                    {
                        int intStartRow = 0;
                        while (true)
                        {
                            OrderDetail newOrderDetail = new OrderDetail();
                            string strSQL = "select top 0 * from Seorderentry ";
                            DataRow drNewDtl_K3 = dbcon.GetDataTable(strSQL).NewRow();

                            foreach (EX_Bill bill in dtConfigure.ToArray())
                            {
                                if (intStartRow == 0)
                                {
                                    intStartRow = bill.ExRow;
                                    intRow = intStartRow;
                                }

                                intCol = bill.ExCol;
                                IRow row = sheet.GetRow(intRow - 1);//获取工作表第一行
                                if (row == null)
                                {
                                    return true;
                                    break;
                                }
                                ICell cell = row.GetCell(intCol - 1);//获取行的第一列 



                                try
                                {
                                    if (cell == null)
                                    {
                                        return true;
                                        break;
                                    }
                                    if (bill.KField.ToString().ToUpper() == "FItemID".ToString().ToUpper())
                                    {
                                        string strItemNumber = cell.ToString();
                                        if (CvtFunc.ToString(cell.ToString()) == "" && intRow == intStartRow)
                                        {
                                            throw new MaxLeafException("表体没有内容.");
                                        }
                                        else if (CvtFunc.ToString(cell.ToString()) == "")
                                        {
                                            return true;
                                            break;
                                        }
                                        else
                                        {
                                            string strFItemID = dbcon.GetValue("select fitemid from t_icitem where fnumber='" + strItemNumber + "' and FDeleted=0");
                                            if (strFItemID == "")
                                            {
                                                throw new MaxLeafException("不存在 " + CvtFunc.ToString(cell.ToString()) +" 或 此项目已被删除. ");
                                            }
                                            else
                                            {
                                                drNewDtl_K3["FItemID"] = strFItemID;
                                            }
                                        }
                                    }
                                    else
                                    {
                                        drNewDtl_K3[bill.KField.ToString()] = cell.ToString();
                                    }
                                }
                                catch (Exception ex)
                                {
                                    throw;
                                }
                            }
                            intRow++;
                            if (!AddDetail(dbcon, drHdr, drNewDtl_K3, newOrderDetail))
                            {
                                return false;
                                break;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw;
                    }
                    finally
                    {

                    }
                }
            }
            return true;
        }

        public void Handle(ImportSalesOrderCommand command)
        {
            using (var dbCon = new DBAccess(new ServerInfo
            {
                Type = ServerType.K3,
                IP = System.Configuration.ConfigurationManager.AppSettings["IP"],
                Port = System.Configuration.ConfigurationManager.AppSettings["Port"],
                Database = command.DBId,
                Uid = System.Configuration.ConfigurationManager.AppSettings["Uid"],
                Pwd = System.Configuration.ConfigurationManager.AppSettings["Pwd"],
            }))
            {
                mstrFieldNamesHDR_K3 = GetTableSQL(dbCon, "seorder").Replace("[", "").Replace("]", "");
                //mstrFieldNamesDTL_K3 = GetTableSQL(dbCon, "seorderentry").Replace("[", "").Replace("]", ""); ;
                dbCon.BeginTrans();
                try
                {
                    string strSQL = "select top 0 * from seOrder";
                    DataRow drNewK3Hdr = dbCon.GetDataTable(strSQL).NewRow();
                    #region 添加到K3的表头
                    drNewK3Hdr["FBrNo"] = "0";
                    drNewK3Hdr["finterid"] = GetFinterID(dbCon);
                    drNewK3Hdr["fbillno"] = GetBillNo(dbCon);
                    drNewK3Hdr["Fcurrencyid"] = "1";
                    drNewK3Hdr["FcustID"] = "0"; // dbWebCon.GetValue("select FItemID from mary_users where user_id='" + CvtFunc.ToString(drWebHdr["user_id"]) + "'");
                    drNewK3Hdr["Fdate"] = DateTime.Now.ToShortDateString();
                    drNewK3Hdr["FsaleStyle"] = "101";

                    //drNewK3Hdr["FHeadSelfS0168"] = DateTime.Now.ToString();

                    drNewK3Hdr["FdeptID"] = CvtFunc.ToString(dbCon.GetValue("select FItemID from t_department where fnumber='1.13.006'"));
                    drNewK3Hdr["FEmpID"] = "10173";
                    drNewK3Hdr["fmangerid"] = drNewK3Hdr["FEmpID"];

                    //drNewK3Hdr["Fcheckerid"]=  drNewK3Hdr["FEmpID"];
                    string strCheckerName = "网站审核";
                    //drNewK3Hdr["Fcheckerid"] = CvtFunc.ToInt(dbCon.GetValue("select FuserID from t_Base_User where fname='" + strCheckerName + "'"));
                    drNewK3Hdr["fbillerid"] = CvtFunc.ToInt(dbCon.GetValue("select FuserID from t_Base_User where fname='" + strCheckerName + "'")); ;

                    //}
                    drNewK3Hdr["Ftrantype"] = "81";
                    drNewK3Hdr["finvoiceclosed"] = "0";
                    drNewK3Hdr["fbclosed"] = "0";

                    drNewK3Hdr["fsettleid"] = "0";
                    drNewK3Hdr["fexchangerate"] = "1";
                    drNewK3Hdr["fdiscounttype"] = false;
                    drNewK3Hdr["fstatus"] = "0";
                    drNewK3Hdr["fcancellation"] = false;
                    //drNewK3Hdr["Fcheckdate"] = drNewK3Hdr["Fdate"];
                    //drNewK3Hdr["Fsettledate"] = drNewK3Hdr["Fdate"];
                    drNewK3Hdr["fseltrantype"] = "0";
                    drNewK3Hdr["fchildren"] = "0";
                    //这两个字段要特殊取
                    drNewK3Hdr["fbrid"] = dbCon.GetValue("select top 1 fbrid from seorder");
                    drNewK3Hdr["fareaps"] = dbCon.GetValue("select top 1 fareaps from seorder");
                    //
                    drNewK3Hdr["fsysstatus"] = "2";

                    ImportFromExcelHdr(dbCon, command, drNewK3Hdr, "seorder");

                    #endregion
                    KeyValuePair<string, IDataParameter[]> kvp = dbCon.CreateParamSql(mstrFieldNamesHDR_K3, "SEOrder", drNewK3Hdr, LogStatus.Add, "finterid");
                    dbCon.ExecuteSql(kvp.Key, kvp.Value);

                    if (ImportFromExcelDtl(dbCon, command, drNewK3Hdr, "seorderentry"))
                    {
                        dbCon.CommitTrans();
                    }
                    else
                    {
                        dbCon.RollbackTrans();
                    }
                }
                catch
                {
                    dbCon.RollbackTrans();
                    throw;
                }
            }
        }


        private bool AddDetail(DBAccess dbCon, DataRow drHdr, DataRow drNewDtl_K3, OrderDetail newOrderDetail)
        {
            int intSeqNo = 1;
            string strSQL = "";
            newOrderDetail.FInterID = CvtFunc.ToString(drHdr["FInterID"]);
            newOrderDetail.FEntryID = (CvtFunc.ToLong(dbCon.GetValue("select isnull(max(fentryid),0) from seorderentry where finterid='" + drHdr["FInterID"] + "'"))+1).ToString() ;
            newOrderDetail.FAuxQty = CvtFunc.ToDouble(drNewDtl_K3["Fauxqty"]);
            newOrderDetail.FItemID = CvtFunc.ToString(drNewDtl_K3["FItemID"]);
            strSQL = @"select top 1 B.Fcoefficient,B.Fmeasureunitid,A.* from t_icItem A 
                       inner join t_measureunit B on A.FunitGroupID= B.FunitGroupID and 
                       A.FItemID='" + drNewDtl_K3["FItemID"] + "' order by Fcoefficient desc";
            DataRow drItem = dbCon.GetDataFirstRow(strSQL);
            if (drItem != null)
            {
                newOrderDetail.FBaseUnitID = CvtFunc.ToString(drItem["FUnitID"]);
                newOrderDetail.FAuxUnitID = CvtFunc.ToString(drItem["Fmeasureunitid"]);
                newOrderDetail.FTaxRate = CvtFunc.ToDouble(drItem["FTaxRate"]);   // 此物料的税率 
                newOrderDetail.FCESS = newOrderDetail.FTaxRate;
                newOrderDetail.FItemTypeID = CvtFunc.ToString(drItem["FTypeID"]); ;
            }
            else
            {
                throw new MaxLeafException("  没有找到与之对应的 销售单位。");
                return false;  //没有找到与之对应的 sales unit.
            }

            double dblRate = 1;
            double dblRate2 = 1;  //取价格的时候，返回的unitid
            string strFunit = "";
            double dblPrice = 0;
            double dblPrice_ByCust = 0;
            if (newOrderDetail.FBaseUnitID != newOrderDetail.FAuxUnitID)
            {
                strSQL = "select * from t_measureunit where FunitGroupID='{0}'and Fmeasureunitid='{1}'";
                strSQL = string.Format(strSQL, CvtFunc.ToString(drItem["FunitGroupID"]), newOrderDetail.FAuxUnitID);
                DataRow drRate = dbCon.GetDataFirstRow(strSQL);
                if (drRate != null)
                {
                    dblRate = CvtFunc.ToDouble(drRate["Fcoefficient"]);
                }
            }
            newOrderDetail.dblRate = dblRate;

            #region 更新重量
            strSQL = "select FGrossWeight,FGrossWeight,FSaleUnitID from t_icitem where fitemid='{0}'";
            strSQL = string.Format(strSQL, newOrderDetail.FItemID); 
            DataRow drWeight = dbCon.GetDataFirstRow(strSQL);
            if (drWeight != null)
            {
                strSQL = "select * from t_measureunit where FunitGroupID='{0}'and Fmeasureunitid='{1}'";
                strSQL = string.Format(strSQL, CvtFunc.ToString(drItem["FunitGroupID"]), drWeight["FSaleUnitID"]);
                DataRow drSalesRate = dbCon.GetDataFirstRow(strSQL);
                if (drSalesRate != null)
                {
                    double dblWeightRate = CvtFunc.ToDouble(drSalesRate["Fcoefficient"]);
                    if (dblWeightRate > 0)
                    {
                        newOrderDetail.FEntrySelfS0170 = CvtFunc.ToString(newOrderDetail.FAuxQty * dblRate / dblWeightRate * CvtFunc.ToDouble(drWeight["FGrossWeight"]));
                    }
                }
            }
            #endregion


            #region 这个地方要写取价格的处理  , strFunit 是价格政策中的单位
            dblPrice = GetDefPrice(dbCon, newOrderDetail, drHdr, ref strFunit);
            if (newOrderDetail.FBaseUnitID != strFunit) // 取的不是基本单位
            {
                strSQL = "select * from t_measureunit where FunitGroupID='{0}'and Fmeasureunitid='{1}'";
                strSQL = string.Format(strSQL, CvtFunc.ToString(drItem["FunitGroupID"]), strFunit);
                DataRow drRate = dbCon.GetDataFirstRow(strSQL);
                if (drRate != null)
                {
                    dblRate2 = CvtFunc.ToDouble(drRate["Fcoefficient"]);
                }
            }
            dblPrice = dblRate * dblPrice / dblRate2; // 转到销售单位
            #endregion

            newOrderDetail.FBasePrice = dblPrice / (1.00 + newOrderDetail.FTaxRate * 0.01) / dblRate;
            newOrderDetail.FAuxPrice = dblRate * newOrderDetail.FBasePrice;
            newOrderDetail.FAuxTaxPrice = dblPrice;


            newOrderDetail.FDisRate = GetDefDiscount(dbCon, newOrderDetail, drHdr);
            newOrderDetail.FBaseQty = newOrderDetail.FAuxQty * dblRate;
            newOrderDetail.FBaseAmount = newOrderDetail.FBaseQty * newOrderDetail.FBasePrice * (1 - newOrderDetail.FDisRate * 0.01);

            newOrderDetail.FTaxAmount = newOrderDetail.FBaseAmount * newOrderDetail.FTaxRate * 0.01;
            newOrderDetail.FDisAmt = newOrderDetail.FBaseQty * dblPrice * newOrderDetail.FDisRate * 0.01;


            newOrderDetail.FTaxPrice = dblPrice / dblRate;
            newOrderDetail.FEntrySelfS0163 = newOrderDetail.FAuxQty * dblPrice; //newOrderDetail.FEntrySelfS0161;
            newOrderDetail.FDisPrice = dblPrice * (1 - newOrderDetail.FDisRate * 0.01);

            intSeqNo++;
            newOrderDetail.FEntrySelfS0161 = dblPrice;
            newOrderDetail.FEntrySelfS0162 = dblPrice * (1 - newOrderDetail.FDisRate * 0.01);
            #region 把newOrderDetail设置给DATAROW
            if (!SetOrderDetailToDataRow(newOrderDetail, ref drNewDtl_K3))
            {
                throw new MaxLeafException("赋值到DataRow中出错.添加销售单失败。");
                return false;
            }
            #endregion
            //mcurOrder.Detail.Add(newOrderDetail);  
            KeyValuePair<string, IDataParameter[]> kvp = dbCon.CreateParamSql(mstrFieldNamesDTL_K3, "seorderentry", drNewDtl_K3, LogStatus.Add, "FDetailID");
            dbCon.ExecuteSql(kvp.Key, kvp.Value);





            return true;
        }


        private bool SetOrderDetailToDataRow(OrderDetail newOrderDetail, ref DataRow drNewDtl_K3)
        {
            drNewDtl_K3["FBrNO"] = "0";
            drNewDtl_K3["FInterID"] = CvtFunc.ToInt(newOrderDetail.FInterID);
            drNewDtl_K3["FEntryID"] = CvtFunc.ToInt(newOrderDetail.FEntryID);
            drNewDtl_K3["FItemID"] = CvtFunc.ToInt(newOrderDetail.FItemID);
            drNewDtl_K3["FQty"] = newOrderDetail.FBaseQty;
            drNewDtl_K3["FCommitQty"] = "0";
            drNewDtl_K3["FPrice"] = newOrderDetail.FBasePrice;
            drNewDtl_K3["FAmount"] = newOrderDetail.FBaseAmount;
            drNewDtl_K3["FTaxRate"] = newOrderDetail.FDisRate;
            drNewDtl_K3["FTaxAmount"] = newOrderDetail.FAuxQty * newOrderDetail.FAuxTaxPrice * newOrderDetail.FDisRate * 0.01;  //折扣金额
            drNewDtl_K3["FTax"] = "0";
            drNewDtl_K3["FDiscount"] = "0";
            drNewDtl_K3["FNote"] = "";
            drNewDtl_K3["FDate"] = DateTime.Now.ToShortDateString();
            drNewDtl_K3["FAdviceConsignDate"] = DateTime.Now.ToShortDateString();
            drNewDtl_K3["FDiscountAmount"] = newOrderDetail.FTaxAmount;
            drNewDtl_K3["FInvoiceQty"] = "0";
            drNewDtl_K3["FBCommitQty"] = "0";
            drNewDtl_K3["FTranLeadTime"] = "0";
            drNewDtl_K3["FATPDeduct"] = "0";
            drNewDtl_K3["FCostObjectID"] = "0";
            drNewDtl_K3["FUnitID"] = newOrderDetail.FAuxUnitID;
            drNewDtl_K3["FAuxPrice"] = newOrderDetail.FAuxPrice;
            drNewDtl_K3["FAuxQty"] = newOrderDetail.FAuxQty;
            drNewDtl_K3["FCESS"] = newOrderDetail.FCESS;
            drNewDtl_K3["FAllAmount"] = newOrderDetail.FDisPrice * newOrderDetail.FAuxQty;
            drNewDtl_K3["FAllStdAmount"] = drNewDtl_K3["FAllAmount"];
            drNewDtl_K3["FTaxAmt"] = newOrderDetail.FTaxAmount;
            drNewDtl_K3["FAuxPriceDiscount"] = newOrderDetail.FDisPrice;//newOrderDetail.FAuxTaxPrice;
            drNewDtl_K3["FPriceDiscount"] = newOrderDetail.FDisPrice / newOrderDetail.dblRate;

            drNewDtl_K3["FAuxTaxPrice"] = newOrderDetail.FAuxTaxPrice;
            drNewDtl_K3["FTaxPrice"] = newOrderDetail.FTaxPrice;
            //drNewDtl_K3["FDetailID,
            //rNewDtl_K3["FMrpAutoClosed,
            //drNewDtl_K3["FSecStockQty,
            //drNewDtl_K3["FSecInvoiceQty,
            //drNewDtl_K3["FSecCommitInstall,

            drNewDtl_K3["FStockQty"] = 0; //newOrderDetail.FBaseQty;
            drNewDtl_K3["FAuxStockQty"] = 0; // newOrderDetail.FAuxQty;
            //drNewDtl_K3["FcommitQty"] = newOrderDetail.FBaseQty; 
            drNewDtl_K3["FPlanMode"] = "14036";
            drNewDtl_K3["FEntrySelfS0161"] = newOrderDetail.FEntrySelfS0161 / newOrderDetail.dblRate;
            drNewDtl_K3["FEntrySelfS0162"] = newOrderDetail.FEntrySelfS0162 / newOrderDetail.dblRate;
            drNewDtl_K3["FEntrySelfS0163"] = newOrderDetail.FEntrySelfS0163;

            drNewDtl_K3["FEntrySelfS0168"] = newOrderDetail.FEntrySelfS0168;
            drNewDtl_K3["FEntrySelfS0171"] = newOrderDetail.FEntrySelfS0171;
            drNewDtl_K3["FEntrySelfS0170"] = newOrderDetail.FEntrySelfS0170;
            drNewDtl_K3["FMrpClosed"] = 0;
            return true;
        }

    }
}
